#!/bin/bash
# ================================================================
# 客户表业务字段优化执行脚本
# ================================================================

echo "=========================================="
echo "  客户表业务字段优化脚本"
echo "=========================================="
echo ""

# 配置数据库连接
DB_HOST="127.0.0.1"
DB_USER="root"
DB_PASS=""
DB_NAME="oa"

# 确认执行
read -p "是否开始执行优化？(y/n): " confirm
if [[ $confirm != [yY] ]]; then
    echo "已取消执行"
    exit 0
fi

echo ""
echo "开始执行数据库优化..."
echo ""

# 执行SQL脚本
mysql -h${DB_HOST} -u${DB_USER} ${DB_PASS:+-p${DB_PASS}} ${DB_NAME} < update_customer_business_fields.sql

if [ $? -eq 0 ]; then
    echo ""
    echo "=========================================="
    echo "  优化执行完成！"
    echo "=========================================="
    echo ""
    echo "已完成的操作："
    echo "✓ 为oa_customer表添加了业务字段"
    echo "✓ 创建了6个触发器自动维护数据"
    echo "✓ 初始化了现有客户数据"
    echo ""
    echo "新增字段："
    echo "  - is_member (是否会员)"
    echo "  - membership_end_time (会员到期时间)"
    echo "  - membership_days_remaining (剩余天数)"
    echo "  - contract_num (合同数量)"
    echo "  - contract_amount (合同总金额)"
    echo "  - latest_contract_type (最新合同类型)"
    echo "  - latest_contract_sign_time (最新合同签订时间)"
    echo "  - case_num (案件总数量)"
    echo "  - case_processing (进行中案件)"
    echo "  - case_finished (已结案案件)"
    echo "  - latest_case_title (最新案件标题)"
    echo "  - latest_case_status (最新案件状态)"
    echo "  - latest_case_update_time (最新案件更新时间)"
    echo ""
    echo "触发器："
    echo "  ✓ tr_contract_insert_update_customer"
    echo "  ✓ tr_contract_update_update_customer"
    echo "  ✓ tr_contract_delete_update_customer"
    echo "  ✓ tr_lawcase_insert_update_customer"
    echo "  ✓ tr_lawcase_update_update_customer"
    echo "  ✓ tr_lawcase_delete_update_customer"
    echo ""
else
    echo ""
    echo "=========================================="
    echo "  执行失败！"
    echo "=========================================="
    echo "请检查数据库连接配置和权限"
    exit 1
fi
